package com.data.data_observer_api.dao;

import com.data.data_observer_api.dataobject.FollowerDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface FollowerDAO extends JpaRepository<FollowerDO,Long> {

    @Query(value = "SELECT\n" +
            "   uid,\n" +
            "   follower,\n" +
            "   data_time \n" +
            "FROM\n" +
            "   o_date_follower \n" +
            "WHERE\n" +
            "   uid=?1 AND\n" +
            "   DATE(data_time) >= DATE_SUB( (SELECT DATE(data_time) FROM o_date_follower ORDER BY data_time desc LIMIT 1) , INTERVAL 7 DAY ) \n" +
            "   AND DATE(data_time) <= (SELECT DATE(data_time) FROM o_date_follower ORDER BY data_time desc LIMIT 1)", nativeQuery = true)
    List<Map<String, Object>> getFollower(Long uid);

    @Query(value = "SELECT\n" +
            "   t1.uid,\n" +
            "   t1.follower,\n" +
            "   t1.data_time,\n" +
            "   t2.follower AS previous_follower,\n" +
            "   t2.data_time AS previous_date_time,\n" +
            "   t1.follower - t2.follower AS follower_difference ,\n" +
            "   t3.`name`,\n" +
            "   t3.face\n" +
            "FROM\n" +
            "   o_date_follower t1\n" +
            "   LEFT JOIN o_date_follower t2 ON t1.uid = t2.uid \n" +
            "   AND DATE ( t2.data_time ) = DATE_SUB(  ( SELECT DATE ( data_time ) FROM o_date_follower ORDER BY data_time DESC LIMIT 1 ), INTERVAL ?1 DAY )\n" +
            "   LEFT JOIN o_account_info t3 ON t1.uid = t3.uid \n" +
            "WHERE\n" +
            "   DATE ( t1.data_time ) =  ( SELECT DATE ( data_time ) FROM o_date_follower ORDER BY data_time DESC LIMIT 1 ) \n" +
            "ORDER BY\n" +
            "   follower_difference DESC \n" +
            "   LIMIT 50;", nativeQuery = true)
    List<Map<String, Object>> getRankDesc(Integer day);

    @Query(value = "SELECT\n" +
            "   t1.uid,\n" +
            "   t1.follower,\n" +
            "   t1.data_time,\n" +
            "   t2.follower AS previous_follower,\n" +
            "   t2.data_time AS previous_date_time,\n" +
            "   t1.follower - t2.follower AS follower_difference ,\n" +
            "   t3.`name`,\n" +
            "   t3.face\n" +
            "FROM\n" +
            "   o_date_follower t1\n" +
            "   LEFT JOIN o_date_follower t2 ON t1.uid = t2.uid \n" +
            "   AND DATE ( t2.data_time ) = DATE_SUB(  ( SELECT DATE ( data_time ) FROM o_date_follower ORDER BY data_time DESC LIMIT 1 ), INTERVAL ?1 DAY )\n" +
            "   LEFT JOIN o_account_info t3 ON t1.uid = t3.uid \n" +
            "WHERE\n" +
            "   DATE ( t1.data_time ) =  ( SELECT DATE ( data_time ) FROM o_date_follower ORDER BY data_time DESC LIMIT 1 ) \n" +
            "ORDER BY\n" +
            "   follower_difference ASC \n" +
            "   LIMIT 50;", nativeQuery = true)
    List<Map<String, Object>> getRankAsc(Integer day);

    @Query(value = "SELECT\n" +
            "   t1.uid,\n" +
            "   t1.follower,\n" +
            "   t1.data_time,\n" +
            "   t2.follower AS previous_follower,\n" +
            "   t2.data_time AS previous_date_time,\n" +
            "   t1.follower - t2.follower AS follower_difference,\n" +
            "   t3.`name`,\n" +
            "   t3.face \n" +
            "FROM\n" +
            "   o_date_follower t1\n" +
            "   LEFT JOIN o_date_follower t2 ON t1.uid = t2.uid \n" +
            "   AND DATE ( t2.data_time ) = DATE_SUB(  ( SELECT DATE ( data_time ) FROM o_date_follower ORDER BY data_time DESC LIMIT 1 ), INTERVAL 1 DAY )\n" +
            "   LEFT JOIN o_account_info t3 ON t1.uid = t3.uid \n" +
            "WHERE\n" +
            "   DATE ( t1.data_time ) =  ( SELECT DATE ( data_time ) FROM o_date_follower ORDER BY data_time DESC LIMIT 1 ) \n" +
            "ORDER BY\n" +
            "   t1.follower DESC \n" +
            "   LIMIT 100;", nativeQuery = true)
    List<Map<String, Object>> getRankTop();
}

